/**
 * 
 */
package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import es.ats.codington.festival.domain.People;
import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.exceptions.DAOException;
import es.ats.codington.festival.interfaces.daos.IVisitorDAO;
import es.ats.codington.festival.utils.FERSDataConnection;
import es.ats.codington.festival.utils.FERSDbQuery;



/**
 * @author Codington_RED
 *
 */ 
public class VisitorDAOImpl implements IVisitorDAO{
	
	private Connection conn;
	public int key;
	
	/**
	 * Default constructor for VisitorDAOImpl
	 * @param conn
	 */
	public VisitorDAOImpl(Connection conn){
		this.conn = conn;
	}
	/**
	 * obtain the personal ID from People database table.
	 * @param p
	 * @return idPeople
	 */
	@Override
	public int getIdPeople(People p) {
		int idPeople = 0;
		
		//Connection conn2 = FERSDataConnection.getConnection();
		PreparedStatement ps2 = null;
		
		try {
			ps2 = conn.prepareStatement("SELECT idPeople FROM people WHERE dni=?;");
			ps2.setString(1, p.getDni());
			
			ResultSet keyPeople = ps2.executeQuery();
			idPeople = keyPeople.getInt(1);
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps2);
		}
		
		return idPeople;
	}
	
	
	/**
	 * obtain the personal ID from Visitor database table. It should be the same as people table.
	 * @param v
	 * @return idVisitor
	 */
	
	@Override
	public int getIdVisitor(Visitor v) {
		int idVisitor = 0;
		
		//Connection conn2 = FERSDataConnection.getConnection();
		PreparedStatement ps3 = null;
		
		try {
			ps3 = conn.prepareStatement("SELECT idVisitor FROM visitor WHERE userName=?;");
			ps3.setString(1, v.getUserName());
			
			ResultSet keyVisitor = ps3.executeQuery();
			keyVisitor.first();
			idVisitor = keyVisitor.getInt("idVisitor");
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps3);
		}
		
		return idVisitor;
	}
	
	/**
	 * Obtains a list with the visitors registered in 
	 * @param resultSet
	 * @return visitors
	 * @throws SQLException
	 */
	private List<Visitor> getVisitorList(ResultSet resultSet) throws SQLException {
			
		 List<Visitor> visitors = new ArrayList<Visitor>();
			
		 while(resultSet.next()) {	      
			 Visitor visitor = new Visitor();
			
			 
			 visitor.setVisitorId(resultSet.getInt("idVisitor"));
			 visitor.setUserName(resultSet.getString("userName"));
			 visitor.setPassword(resultSet.getString("password"));
			 visitor.setAdmin(resultSet.getBoolean("admin"));
			 
			 visitors.add(visitor); 
			    
		}
			
		return visitors;
    }
	
	/**
	 * obtains a list with the people.
	 * @param resultSet
	 * @return peoples
	 * @throws SQLException
	 */
	private List<People> getPeopleList(ResultSet resultSet) throws SQLException {
		
		 List<People> peoples = new ArrayList<People>();
			
		 while(resultSet.next()) {	      
			 People people = new People();
			
			 people.setIdPeople(resultSet.getInt("idPeople"));
			 people.setFirstName(resultSet.getString("firstName"));
			 people.setLastName(resultSet.getString("lastName"));
			 people.setDni(resultSet.getString("dni"));
			 people.setEmail(resultSet.getString("email"));
			 people.setPhoneNumber(resultSet.getString("phoneNumber"));
			 people.setAddress(resultSet.getString("address"));
			 
			 peoples.add(people);
			    
		}
			
		return peoples;
   }
	
	/**
	 * obtains a visitor.
	 * @param resultSet
	 * @return visitor
	 * @throws SQLException
	 */
	@Override
	public Visitor getVisitor(ResultSet resultSet) throws SQLException {
		
		List<Visitor> visitors = this.getVisitorList(resultSet);	
		Visitor visitor = null;
		
		if(!visitors.isEmpty()) {
		    visitor = visitors.get(0);
		    
		}
				
		return visitor;
	}
	
	/**
	 * Retrieves all the people registered in the system.
	 * @param resultSet
	 * @return people
	 * @throws SQLException
	 */
	@Override
	public People getPeople(ResultSet resultSet) throws SQLException {
		
		List<People> peoples = this.getPeopleList(resultSet);	
		People people = null;
		
		if(!peoples.isEmpty()) {
		    people = peoples.get(0);
		    
		}
				
		return people;
	}
	
	
	/**
	 * Confirms that login have been done right
	 * @param visitor
	 * @return isAdmin 
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public boolean getCheckLogin(Visitor visitor) throws DAOException, SQLException {
		boolean isAdmin = false;
		//Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getCheckLogin();
		ResultSet rs = null;
		
		try{
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, visitor.getUserName());
			ps.setString(2, visitor.getPassword());
			
			//System.out.println(visitor.getUserName());
			//System.out.println( visitor.getPassword());


			
			
			rs = ps.executeQuery();
			//System.out.println(rs.getRow());
			//rs.last();
			
			
			
			}catch(SQLException ex) {
				throw new DAOException(ex.getMessage(), ex);
			}
		
		rs.first();
		if (rs.getRow() == 1) {
			isAdmin = true;
		}
		
		
		
		return isAdmin;
	}
	/**
	 * check if the visitor that has just entered to the system is an administrator
	 * @param visitor
	 * @return isLogin
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public boolean getCheckAdmin(Visitor visitor) throws DAOException, SQLException {
		boolean isLogin = false;
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getCheckAdmin();
		ResultSet rs = null;
		
		try{
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, visitor.getUserName());
			ps.setString(2, visitor.getPassword());
			
		
			rs = ps.executeQuery();
			//System.out.println(rs.getRow());
			//rs.last();
			
			
			
			}catch(SQLException ex) {
				throw new DAOException(ex.getMessage(), ex);
		}
		
		rs.first();
		//rs.getBoolean("admin");
	
		//System.out.println(rs.getBoolean("admin"));
		
		if (rs.getBoolean("admin")){
			isLogin = true;
		}
		
		//if (rs.getRow() == 1) {
			//isLogin = true;
		//}
		
		
		
		return isLogin;
	}
	/**
	 * check if the people logged exists at the database table
	 * @param people
	 * @return isLogin
	 * @throws DAOException
	 * @throws SQLException
	 */
	@Override
	public boolean getCheckPeople(People people) throws DAOException, SQLException {
		boolean isLogin = false;
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getCheckPeople();
		ResultSet rs = null;
		
		try{
			
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, people.getDni());
			
			
			//System.out.println(people.getDni());
	
			
			rs = ps.executeQuery();
			//System.out.println(rs.getRow());
			//rs.last();
			
			
			
			}catch(SQLException ex) {
				throw new DAOException(ex.getMessage(), ex);
			}
		
		rs.first();
		
		if (rs.getRow() == 1) {
			isLogin = true;
		}
		
		
		
		return isLogin;
	}
	/**
	 * method that allows us to introduce people data into the System
	 * @param people
	 * @return insertPeople
	 * @throws DAOException
	 */
	@Override
	public boolean insertData(People people) throws DAOException{
		
		boolean insertPeople = false;
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getInsertPeople();
		
		try {
			
			
			ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);			
			
			ps.setString(1, people.getFirstName());
			ps.setString(2, people.getLastName());
			ps.setString(3, people.getDni());
			ps.setString(4, people.getEmail());
			ps.setString(5, people.getPhoneNumber());
			ps.setString(6, people.getAddress());
			
			
			
			//Executing this query using executeUpdate()
			affectedRows = ps.executeUpdate();
			
			//conn.commit();
			ResultSet keys = ps.getGeneratedKeys();
			keys.next();
			key = keys.getInt(1);
			
			people.setIdPeople(key);
			
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps);
		}
		
		if (affectedRows > 0) {
			insertPeople = true;
		}
		
		return insertPeople;
	}

	/**
	 * method that allows us to introduce visitor information into the System
	 * @param visitor
	 * @return insertVisitor
	 * @throws DAOExcpetion
	 */
	@Override
	public boolean insertDataV(Visitor visitor) throws DAOException {
		boolean insertVisitor = false;
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getInsertVisitor();
		
		try {
			
			
			ps = conn.prepareStatement(sql);			
			
			//ps.setInt(1, visitor.getVisitorId());
			ps.setInt(1, visitor.getVisitorId());
			//ps.setInt(1, visitor.getVisitorId());
			ps.setString(2, visitor.getUserName());
			ps.setString(3, visitor.getPassword());
			ps.setBoolean(4, visitor.isAdmin());
			
			
			//Executing this query using executeUpdate()
			affectedRows = ps.executeUpdate();
			
			//visitor.setVisitorId(key);
			
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps);
		}
		
		if (affectedRows > 0) {
			insertVisitor = true;
		}
		
		return insertVisitor;
	}
	
	/**
	 * method that allows us to introduce people information into the System
	 * @param visitor
	 * @return insertVisitor
	 * @throws DAOException
	 */
	@Override
	public boolean insertData(Visitor visitor) throws DAOException {
		
		boolean insertVisitor = false;
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getInsertVisitor();
		
		try {
			
			
			ps = conn.prepareStatement(sql);			
			
			//ps.setInt(1, visitor.getVisitorId());
			ps.setInt(1, key);
			//ps.setInt(1, visitor.getVisitorId());
			ps.setString(2, visitor.getUserName());
			ps.setString(3, visitor.getPassword());
			ps.setBoolean(4, visitor.isAdmin());
			
			
			//Executing this query using executeUpdate()
			affectedRows = ps.executeUpdate();
			
			visitor.setVisitorId(key);
			
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps);
		}
		
		if (affectedRows > 0) {
			insertVisitor = true;
		}
		
		return insertVisitor;

	}

	/**
	 * look for a visitor into the database table.
	 * @param visitor
	 * @return v
	 * @throws DAOException
	 */
	@Override
	public Visitor searchVisitor(Visitor visitor) throws DAOException {
		
		Visitor v= null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = FERSDbQuery.getSearchVisitor();
			
		try {
		    ps = conn.prepareStatement(sql);
		    
		    
		    //ps.setInt(1, key);
		    
		    ps.setInt(1, visitor.getVisitorId());
		    
		    rs = ps.executeQuery();
		    
		    v = this.getVisitor(rs);
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {
		    FERSDataConnection.closeStatement(ps);
		    FERSDataConnection.closeResultSet(rs);
		    
		}	
			
		return v;
	}
	
	/**
	 * look for people existing inside of database table
	 * @param people
	 * @return p
	 * @throws DAOException
	 */
	@Override
	public People searchPeople(People people) throws DAOException {
		
		People p= null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = FERSDbQuery.getSearchPeople();
			
		try {
		    ps = conn.prepareStatement(sql);
		    
		    //ps.setInt(1, key);
		    
		    ps.setInt(1, people.getIdPeople());
		    
		    rs = ps.executeQuery();
		    
		    p = this.getPeople(rs);
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {
		    FERSDataConnection.closeStatement(ps);
		    FERSDataConnection.closeResultSet(rs);
		    
		}	
			
		return p;
	}
	/**
	 * update the data about DNI visitor
	 * @param people
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateVisitorDni(People people) throws DAOException {
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getUpdatePeopleDni();
		
		try {
			
			
			ps = conn.prepareStatement(sql);			
			
			ps.setString(1, people.getFirstName());
			ps.setString(2, people.getLastName());
			ps.setString(3, people.getDni());
			ps.setString(4, people.getEmail());
			ps.setString(5, people.getPhoneNumber());
			ps.setString(6, people.getAddress());
			//ps.setInt(7, key);
			ps.setString(7, people.getDni());
			
			
			
			//Executing this query using executeUpdate()
			affectedRows = ps.executeUpdate();
			
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps);
		}
		
		return affectedRows;
	}
	/**
	 * update all the information relative to visitor database table
	 * @param people
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateVisitor(People people) throws DAOException {
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getUpdatePeople();
		
		try {
			
			
			ps = conn.prepareStatement(sql);			
			
			ps.setString(1, people.getFirstName());
			ps.setString(2, people.getLastName());
			ps.setString(3, people.getDni());
			ps.setString(4, people.getEmail());
			ps.setString(5, people.getPhoneNumber());
			ps.setString(6, people.getAddress());
			//ps.setInt(7, key);
			ps.setInt(7, people.getIdPeople());
			
			
			
			//Executing this query using executeUpdate()
			affectedRows = ps.executeUpdate();
			
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps);
		}
		
		return affectedRows;
	}

	/**
	 * update all the information relative to visitor database table
	 * @param visitor
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updateVisitor(Visitor visitor) throws DAOException {
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		String sql = FERSDbQuery.getUpdateVisitor();
		
		try {
			
			
			ps = conn.prepareStatement(sql);			
			
			
			ps.setString(1, visitor.getUserName());
			ps.setString(2, visitor.getPassword());
			ps.setBoolean(3, visitor.isAdmin());
			
			
			//ps.setInt(4, key);
			
			ps.setInt(4, visitor.getVisitorId());
			
			//Executing this query using executeUpdate()
			affectedRows = ps.executeUpdate();
			
			
		} catch (SQLException ex) {
			throw new DAOException(ex.getMessage(), ex);
			
		} finally{
			FERSDataConnection.closeStatement(ps);
		}
		
		return affectedRows;
	}
	
	/**
	 * changes or updates the visitor password
	 * @param visitor
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int changePassword(Visitor visitor) throws DAOException {
		Integer affectedRows = 0;
		PreparedStatement ps = null;
		
		String sql = FERSDbQuery.getChangePasswordVisitor();
					 			
		try {
		    ps = conn.prepareStatement(sql);
		    
		    ps.setString(1, visitor.getPassword());
		    ps.setString(2, visitor.getUserName());
		    
		    affectedRows = ps.executeUpdate();
		    System.out.println();
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {	   
		    FERSDataConnection.closeStatement(ps);
		    
		}
		
		return affectedRows;
	}
	
}
